﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Web.Services;
using System.Xml;

namespace WS_Misto
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://vecirek.char-t.com/xml/misto/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
        // [System.Web.Script.Services.ScriptService]
    public class Service1 : WebService
    {
        private static OracleConnection Connection;

        [WebMethod]
        public string HelloWorld()
        {
            return "\n[Misto] Hello World!";
        }

        private static string GetConnectionString()
        {
            return "User ID=hejlt6am;Password=heslo;Unicode=True;Persist Security Info=True;" +
                   "Data Source=(DESCRIPTION=" +
                   "(ADDRESS=(PROTOCOL=TCP)(HOST=tirpitz.ms.mff.cuni.cz)(PORT=1511))" +
                   "(CONNECT_DATA=(SID=xml)));"; // zmena portu z 1521, ten funguje jen z ms sítě
        }

        [WebMethod]
        public XmlElement getSuitableMisto(string styl, int pocetOsob)
        {
            return getMistoFromDB(styl, pocetOsob).DocumentElement;
        }

        /// <summary>
        /// Vrati XmlDocument obsahujuci <list><budova>...</budova>*</list>, nacitane z db
        /// </summary>
        public XmlDocument getMistoFromDB(string styl, int pocetOsob)
        {
            bool shouldDisconnect = !IsConnected;
            var doc = new XmlDocument();

            if (IsConnected || Connect())
            {
                try
                {
                    OracleCommand command = Connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = "select misto.*, misto_styl.styl from misto_styl join misto on misto.id = misto_styl.id where misto_styl.styl = '" + styl + "' and misto.kapacita >= " + pocetOsob;
                  
                    OracleDataReader ord = command.ExecuteReader();
					          string list = "";
                    while (ord.Read())
                    {
                      list += "<record><id>" +  ord.GetInt32(ord.GetOrdinal("id")) + "</id>"
								          + "<nazev>" + ord.GetString(ord.GetOrdinal("nazev")) + "</nazev>"
								          + "<adresa>" + ord.GetString(ord.GetOrdinal("adresa")) + "</adresa>"
                          + "<kapacita>" + ord.GetInt32(ord.GetOrdinal("kapacita")) + "</kapacita>"
                          + "<cena>" + ord.GetInt32(ord.GetOrdinal("cena")) + "</cena>"
                          + "<styly><styl>" + ord.GetString(ord.GetOrdinal("styl")) + "</styl></styly></record>";
                    }
                    list = "<list>" + list + "</list>";
                    doc.LoadXml(list);
                    return doc;
                }
                catch (Exception e)
                {
                  doc.LoadXml("<exception>" + e.Message + "</exception>");
                  return doc;
                }
                finally
                {
                    if (shouldDisconnect) Disconnect();
                }
            }
            doc.LoadXml("<exception>Unconnected</exception>");
            return doc;
        }

        #region connect/dc

        public static bool IsConnected
        {
            get
            {
                return (Connection != null) &&
                       (Connection.State != ConnectionState.Broken) &&
                       (Connection.State != ConnectionState.Closed);
            }
        }

        public static bool Connect()
        {
            Connection = new OracleConnection {ConnectionString = GetConnectionString()};

            try
            {
                Connection.Open();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static bool Disconnect()
        {
            if (Connection != null)
            {
                try
                {
                    if ((Connection.State != ConnectionState.Closed) &&
                        (Connection.State != ConnectionState.Broken))
                    {
                        Connection.Close();
                    }

                    Connection = null;
                    return true;
                }
                catch (Exception e)
                {
                    return false;
                }
            }
            return true;
        }

        #endregion
    }
}